package devicedbgui.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

/** Interface to database for acquiring Repair information */
public class RepairInfoDAO {
     /** Stores Connection status */
    private Connection conn;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    /** Constructor used to pass connection status to object
     * @param conn  connection status
     */
    public RepairInfoDAO(Connection conn)
    {
        this.conn = conn;
    }
    /** Selects all repair information in database
     * 
     * @throws java.lang.Exception
     */
    public ArrayList<RepairInfo> selectAll() throws Exception {
        ArrayList<RepairInfo> result = new ArrayList<RepairInfo>();

        try {
            String query = "SELECT * FROM RepairInfo;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                RepairInfo pObj = new RepairInfo(
                        rs.getInt("idRepairInfo"),
                        rs.getTimestamp("timeEntered"),                        
                        rs.getString("description"),                        
                        rs.getString("timeSpent"),                        
                        rs.getString("changesMade"),                        
                        rs.getString("costEstimate"),                        
                        rs.getInt("Build_idBuild")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
    /** Selects all repair info
     * 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<RepairInfo> selectAllEx() throws Exception {
        ArrayList<RepairInfo> result = new ArrayList<RepairInfo>();

        try {
            String query = "SELECT ri.*, d.name, d.serial FROM RepairInfo ri, Build b, Device d " + 
                    "where ri.Build_idBuild = b.idBuild and b.Device_record = d.record" + 
                    ";";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                RepairInfo pObj = new RepairInfo(
                        rs.getInt("idRepairInfo"),
                        rs.getTimestamp("timeEntered"),                        
                        rs.getString("description"),                        
                        rs.getString("timeSpent"),                        
                        rs.getString("changesMade"),                        
                        rs.getString("costEstimate"),                        
                        rs.getInt("Build_idBuild")
                        );
                pObj.deviceName = rs.getString("name");
                pObj.deviceSerial = rs.getString("serial");
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
     /** Inserts new repair info into database
     * 
     * @param pObj
     * @throws java.lang.Exception
     */
    public void insert(RepairInfo pObj) throws Exception {
       
        try {
            String insert = "INSERT INTO RepairInfo(" +
                    "timeEntered," +
                    "description," +
                    "timeSpent," +
                    "changesMade," +
                    "costEstimate," +
                    "Build_idBuild)" +
                    "VALUES('" + sdf.format(pObj.timeEntered) + "','" +
                                 pObj.description + "','" +
                                 pObj.timeSpent + "','" +
                                 pObj.changesMade + "','" +
                                 pObj.costEstimate + "','" +
                                 pObj.Build_idBuild + "');";

            
            PreparedStatement pstmt = conn.prepareStatement(insert);
            pstmt.executeUpdate();            

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
/** Updates database with the modified repair information 
 * 
 * @param pObj
 * @throws java.lang.Exception
 */
    public void update(RepairInfo pObj) throws Exception {        
        try {

            String update = "UPDATE RepairInfo SET " +
                    "timeEntered = '" + sdf.format(pObj.timeEntered) + "'," +
                    "description = '" + pObj.description +  "'," +
                    "timeSpent = '" + pObj.timeSpent +  "'," +
                    "changesMade = '" + pObj.changesMade +  "'," +
                    "costEstimate = '" + pObj.costEstimate +  "'," +
                    "Build_idBuild = '" + pObj.Build_idBuild +
                    "' WHERE idRepairInfo = '" + pObj.idRepairInfo + "';";
            
           
            PreparedStatement pstmt = conn.prepareStatement(update);
            pstmt.executeUpdate();
            pstmt.close();
           

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
/** Deletes a value from database
 * 
 * @param pId
 * @throws java.lang.Exception
 */
    public void delete(int pId) throws Exception {
       
        try {
            String delete = "DELETE FROM RepairInfo WHERE idRepairInfo = '" + pId + "';";
          
            PreparedStatement pstmt = conn.prepareStatement(delete);
            pstmt.executeUpdate();
           

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
    /** Chooses repair info based on build id
     * 
     * @param idBuild 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<RepairInfo> selectByBuild(int idBuild) throws Exception {
        ArrayList<RepairInfo> result = new ArrayList<RepairInfo>();
        try {
            String query = "SELECT * FROM RepairInfo where Build_idBuild = " + idBuild +";";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                RepairInfo pObj = new RepairInfo(
                        rs.getInt("idRepairInfo"),
                        rs.getTimestamp("timeEntered"),                        
                        rs.getString("description"),                        
                        rs.getString("timeSpent"),                        
                        rs.getString("changesMade"),                        
                        rs.getString("costEstimate"),                        
                        rs.getInt("Build_idBuild")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    } 
    /** Chooses repair info based on device record
     * 
     * @param deviceRecord 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<RepairInfo> selectByDevice(String deviceRecord) throws Exception {
        ArrayList<RepairInfo> result = new ArrayList<RepairInfo>();
        try {
            String query = "SELECT ri.* FROM RepairInfo ri, Build b where ri.Build_idBuild = b.idBuild and b.Device_record='" + deviceRecord +"';";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                RepairInfo pObj = new RepairInfo(
                        rs.getInt("idRepairInfo"),
                        rs.getTimestamp("timeEntered"),                        
                        rs.getString("description"),                        
                        rs.getString("timeSpent"),                        
                        rs.getString("changesMade"),                        
                        rs.getString("costEstimate"),                        
                        rs.getInt("Build_idBuild")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    } 
    /** Chooses repair info based on build id
     * 
     * @param idBuild 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<RepairInfo> selectByBuildEx(int idBuild) throws Exception {
        ArrayList<RepairInfo> result = new ArrayList<RepairInfo>();
        try {
            String query = "SELECT ri.*, d.name, d.serial FROM RepairInfo ri, Build b, Device d " + 
                    "where ri.Build_idBuild = b.idBuild and b.Device_record = d.record and " +
                    "ri.Build_idBuild = " + idBuild + 
                    ";";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                RepairInfo pObj = new RepairInfo(
                        rs.getInt("idRepairInfo"),
                        rs.getTimestamp("timeEntered"),                        
                        rs.getString("description"),                        
                        rs.getString("timeSpent"),                        
                        rs.getString("changesMade"),                        
                        rs.getString("costEstimate"),                        
                        rs.getInt("Build_idBuild")
                        );
                pObj.deviceName = rs.getString("name");
                pObj.deviceSerial = rs.getString("serial");
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    } 
    
    /** Chooses repair info based on device record
     * 
     * @param deviceRecord 
     * @return result
     * @throws java.lang.Exception
     */public ArrayList<RepairInfo> selectByDeviceEx(String deviceRecord) throws Exception {
        ArrayList<RepairInfo> result = new ArrayList<RepairInfo>();
        try {
            String query = "SELECT ri.*, d.name, d.serial FROM RepairInfo ri, Build b, Device d " + 
                    "where ri.Build_idBuild = b.idBuild and b.Device_record = d.record and " + 
                    "d.record = '" + deviceRecord +
                    "';";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                RepairInfo pObj = new RepairInfo(
                        rs.getInt("idRepairInfo"),
                        rs.getTimestamp("timeEntered"),                        
                        rs.getString("description"),                        
                        rs.getString("timeSpent"),                        
                        rs.getString("changesMade"),                        
                        rs.getString("costEstimate"),                        
                        rs.getInt("Build_idBuild")
                        );
                pObj.deviceName = rs.getString("name");
                pObj.deviceSerial = rs.getString("serial");
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
}
